package com.moinut.studentattendancemanager.model.dao.impl;

import com.moinut.studentattendancemanager.model.dao.IStudentDAO;
import com.moinut.studentattendancemanager.model.vo.db.Student;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author MOILING
 */
public class StudentDAOImpl extends BaseDAOImpl implements IStudentDAO {

    public StudentDAOImpl(Connection connection) {
        super(connection);
    }

    @Override
    public Student findLogin(Student student) throws Exception {
        Student getStudent = null;
        String sql = "SELECT * FROM studentinfo WHERE student_id = ? AND student_login_pwd = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, student.getStudentId());
        preparedStatement.setString(2, student.getStudentPassword());
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            getStudent = new Student(resultSet.getString(1), resultSet.getString(2), resultSet.getString(3),
                    resultSet.getString(4), resultSet.getString(5), resultSet.getString(6));
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return getStudent;
    }

    @Override
    public Student getStudentById(String studentId) throws Exception {
        Student student = null;
        String sql = "SELECT * FROM studentinfo WHERE student_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, studentId);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            student = new Student(resultSet.getString(1), resultSet.getString(2), resultSet.getString(3),
                    resultSet.getString(4), resultSet.getString(5), resultSet.getString(6));
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return student;
    }

    @Override
    public List<Student> getStudentsByName(String studentName) throws Exception {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM studentinfo WHERE student_name LIKE '%" + studentName +"%'";
        preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            Student student = new Student(resultSet.getString(1), resultSet.getString(2), resultSet.getString(3),
                    resultSet.getString(4), resultSet.getString(5), resultSet.getString(6));
            students.add(student);
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return students;
    }

    @Override
    public List<Student> getStudentsByClass(String classId) throws Exception {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM studentinfo WHERE student_id IN (SELECT classstudentinfo.student_id FROM classstudentinfo WHERE classstudentinfo.class_id = ?)";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, classId);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            Student student = new Student(resultSet.getString(1), resultSet.getString(2), resultSet.getString(3),
                    resultSet.getString(4), resultSet.getString(5), resultSet.getString(6));
            students.add(student);
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return students;
    }

    @Override
    public List<Student> getStudentsByMajor(String major) throws Exception {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM studentinfo WHERE student_major LIKE '%" + major + "%'";
        preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            Student student = new Student(resultSet.getString(1), resultSet.getString(2), resultSet.getString(3),
                    resultSet.getString(4), resultSet.getString(5), resultSet.getString(6));
            students.add(student);
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return students;
    }

    @Override
    public List<Student> getAllStudents() throws Exception {
        List<Student> students = new ArrayList<>();
        String sql = "SELECT * FROM studentinfo";
        preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            Student student = new Student(resultSet.getString(1), resultSet.getString(2), resultSet.getString(3),
                    resultSet.getString(4), resultSet.getString(5), resultSet.getString(6));
            students.add(student);
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return students;
    }

    @Override
    public boolean updateStudentInfo(String oldId, Student newStudent) throws Exception {
        String sql = "UPDATE studentinfo " +
                     "SET student_id = ?, student_name = ?, student_college = ?, student_major = ?, student_email = ?" +
                     "WHERE student_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, newStudent.getStudentId());
        preparedStatement.setString(2, newStudent.getStudentName());
        preparedStatement.setString(3, newStudent.getStudentCollege());
        preparedStatement.setString(4, newStudent.getStudentMajor());
        preparedStatement.setString(5, newStudent.getStudentEmail());
        preparedStatement.setString(6, oldId);
        int updateNumber = preparedStatement.executeUpdate();
        if (updateNumber > 0) {
            return true;
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return false;
    }

    @Override
    public boolean updateStudentPassword(String oldId, String newPassword) throws Exception {
        String sql = "UPDATE studentinfo SET student_login_pwd = ?" +
                     "WHERE student_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, newPassword);
        preparedStatement.setString(2, oldId);
        int updateNumber = preparedStatement.executeUpdate();
        if (updateNumber > 0) {
            return true;
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return false;
    }

    @Override
    public boolean updateStudentEmail(String studentId, String newEmail) throws Exception {
        String sql = "UPDATE studentinfo SET student_email = ?" +
                     "WHERE student_id = ?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, newEmail);
        preparedStatement.setString(2, studentId);
        int updateNumber = preparedStatement.executeUpdate();
        if (updateNumber > 0) {
            return true;
        }
        if (this.preparedStatement != null) {
            this.preparedStatement.close();
        }
        return false;
    }
}
